if exists (select 1 from sysobjects where name = 'get_personplanovervieweaders' and type = 'P')
begin
   drop procedure get_personplanovervieweaders
   print 'Procedure: get_personplanovervieweaders deleted ...'
end
go
create procedure get_personplanovervieweaders(
  @prsid      int = 1,
  @jahrid     int = 1
)
as
begin
  set nocount on

  select abteilungid = ps.AbteilungID, 
         abteilungname = a.Name, 
         planid = ap.PlanID, 
         planname = ap.Name,
         monat = at.Name,
         soll = isnull(az.Soll,0),
         haben = isnull(az.Haben,0),
         ferien = isnull(az.Ferien,0),
         anpassung = (select isnull(sum(sa.Stunden),0)
						from Saldoanpassung sa
					   where sa.PrsID = @prsid
						 and sa.PlanID = ap.PlanID
						 and sa.AbteilungID = ps.AbteilungID),
		 prsschemaid = ps.PrsSchemaID,
		 von = ap.Von,
		 bis = ap.Bis
    from PlanSchema ps
    left outer join ArbeitsPlan ap
      on ap.SchemaID = ps.SchemaID
    left outer join PlanungsJahr pj
      on pj.JahrID = ap.JahrID
    left outer join Abteilung a
      on a.AbteilungID = ps.AbteilungID
    left outer join ArbeitsTage at
      on at.ATID = ap.ATID
    left outer join Arbeitszeit az
      on az.PlanID = ap.PlanID
     and az.PrsID = ps.PrsID
     and az.AbteilungID = ps.AbteilungID
   where ps.PrsID = @prsid
     and ap.PlanID is not null
     and pj.JahrID = @jahrid
   order by ps.AbteilungID, ap.PlanID

end
go
print 'Procedure: get_personplanovervieweaders done ...'
go

grant exec on get_personplanovervieweaders to prsadmins with grant option
go
grant exec on get_personplanovervieweaders to prsusers
go

